﻿Imports System.Data.OleDb
Imports System.Text
Imports System.Data.SqlClient
Imports ExcelApp = Microsoft.Office.Interop.Excel
Imports BUS
Imports DTO
Imports DAO
Public Class ExpostToExell



    Public Shared Sub Export(ByVal filepath As String)


        Try
            Dim dt As DataTable = HoaDonBUS.layTatCaTB()
            Dim EApp As New ExcelApp.Application
            Dim EBook As ExcelApp.Workbook

            Dim ESheet As ExcelApp.Worksheet
            Dim oValue As Object = System.Reflection.Missing.Value

            EBook = EApp.Workbooks.Add(oValue)

            ESheet = EBook.Worksheets("Sheet1")
            EApp.Rows.ColumnWidth = 20
            Dim xlRow As Long = 2
            Dim xlCol As Short = 1
            For Each col As DataColumn In dt.Columns
                ESheet.Cells(1, xlCol) = col.ColumnName
                xlCol += 1
            Next


            For Each row As DataRow In dt.Rows
                With ESheet
                    xlCol = 1
                    For Each col As DataColumn In dt.Columns
                        .Cells(xlRow, xlCol) = row(xlCol - 1)
                        xlCol += 1
                    Next
                End With
                xlRow += 1
            Next

            ESheet.SaveAs(filepath)
            EBook.Close()
            EApp.Quit()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub



    Public Shared Sub ExportKH(ByVal filepath As String)


        Try
            Dim dt As DataTable = KhachHangBUS.LayTatCaTB()
            Dim EApp As New ExcelApp.Application
            Dim EBook As ExcelApp.Workbook

            Dim ESheet As ExcelApp.Worksheet
            Dim oValue As Object = System.Reflection.Missing.Value

            EBook = EApp.Workbooks.Add(oValue)

            ESheet = EBook.Worksheets("Sheet1")
            EApp.Rows.ColumnWidth = 20
            Dim xlRow As Long = 2
            Dim xlCol As Short = 1
            For Each col As DataColumn In dt.Columns
                ESheet.Cells(1, xlCol) = col.ColumnName
                xlCol += 1
            Next


            For Each row As DataRow In dt.Rows
                With ESheet
                    xlCol = 1
                    For Each col As DataColumn In dt.Columns
                        .Cells(xlRow, xlCol) = row(xlCol - 1)
                        xlCol += 1
                    Next
                End With
                xlRow += 1
            Next

            ESheet.SaveAs(filepath)
            EBook.Close()
            EApp.Quit()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Shared Sub Import(ByVal file As String)
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=Excel 12.0 Xml;"
        Dim dt As New DataTable

        Try
            Dim con As New OleDbConnection(connString)
            con.Open()
            Dim cmd As New OleDbCommand
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = ("select * from [Sheet1$]")
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            da.SelectCommand = cmd
            da.Fill(dt)
            con.Close()

            Dim cn As New DataProvider()
            If dt.Rows.Count <> 0 And dt.Columns.Count = 7 Then
                For Each dr As DataRow In dt.Rows
                    Dim MaHD As HoaDonDTO = HoaDonBUS.laytheoid(dr.Item("MaHD"))

                Next

                For Each dr As DataRow In dt.Rows
                    Dim para As SqlParameter() = New SqlParameter(5) {}
                    'para(0) = New SqlParameter("@MaHD", dr.Item("MaHD"))
                    para(0) = New SqlParameter("@MaSP", dr.Item("MaSP"))
                    para(1) = New SqlParameter("@ngaylap", dr.Item("NgayLap"))
                    para(2) = New SqlParameter("@SoLuong", dr.Item("SoLuong"))
                    para(3) = New SqlParameter("@MaNV", dr.Item("MaNV"))
                    para(4) = New SqlParameter("@TongTien", dr.Item("TongTien"))
                    para(5) = New SqlParameter("@DonGia", dr.Item("DonGia"))
                    cn.ExecuteNonQuery("InsertHD", para)

                Next
                MessageBox.Show("Nhập dữ liệu từ file excel thành công")
            End If
        Catch ex As Exception
            MessageBox.Show("Lỗi Impost File!", "Lỗi")
        End Try

    End Sub



    Public Shared Sub ImportKH(ByVal file As String)
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=Excel 12.0 Xml;"
        Dim dt As New DataTable

        Try
            Dim con As New OleDbConnection(connString)
            con.Open()
            Dim cmd As New OleDbCommand
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = ("select * from [Sheet1$]")
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            da.SelectCommand = cmd
            da.Fill(dt)
            con.Close()

            Dim cn As New DataProvider()
            If dt.Rows.Count <> 0 And dt.Columns.Count = 5 Then
                For Each dr As DataRow In dt.Rows
                    Dim MaKH As KhachHangDTO = KhachHangBUS.laytheoid(dr.Item("MaKH"))
                  
                Next

                For Each dr As DataRow In dt.Rows
                    Dim para As SqlParameter() = New SqlParameter(4) {}
                    para(0) = New SqlParameter("@MaKH", dr.Item("MaKH"))
                    para(1) = New SqlParameter("@TenKH", dr.Item("TenKH"))
                    para(2) = New SqlParameter("@SDT", dr.Item("SDT"))
                    para(3) = New SqlParameter("@DiaChi", dr.Item("DiaChi"))
                    para(4) = New SqlParameter("@MaHD", dr.Item("MaHD"))

                    cn.ExecuteNonQuery("InsertKHID", para)

                Next
                MessageBox.Show("Nhập dữ liệu từ file excel thành công")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
End Class
